/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import model.Kipmol;

/**
 *
 * @author Jagadeesh
 */
public class DBClass {

    public Connection createConnection() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/fpkipem", "root", "");
        return connection;
    }
    //we get values from servlet by passing bean object to insertdetails method

    public int insertDetails(Kipmol bb) throws SQLException, ClassNotFoundException {
        try {
            Connection con = createConnection();
            PreparedStatement pstmt = con.prepareStatement("insert into userdetails values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            //set values to prepared statement object by getting values from bean object

            pstmt.setString(5, bb.getNamaPemohon());
            pstmt.setString(6, bb.getNIKPemohon());
            pstmt.setString(7, bb.getNoKK());
            pstmt.setString(8, bb.getAlmtPemohon());
            pstmt.setString(9, bb.getRTPemohon());
            pstmt.setString(10, bb.getRWPemohon());
            pstmt.setString(15, bb.getKodePosPemohon());
            pstmt.setString(16, bb.getNoTelpPemohon());
            pstmt.setString(17, bb.getAlasan());
            pstmt.setString(18, bb.getAlmtYgDituju());
            pstmt.setString(19, bb.getRTDituju());
            pstmt.setString(20, bb.getRWDituju());
            pstmt.setString(21, bb.getKodePosDituju());
            pstmt.setString(22, bb.getNoTelpDituju());
            pstmt.setString(23, bb.getNamaOrtu());
            pstmt.setString(24, bb.getAlmtOrtu());
            pstmt.setString(25, bb.getRTOrtu());
            pstmt.setString(26, bb.getRWOrtu());
            pstmt.setString(31, bb.getKodePosOrtu());
            pstmt.setString(32, bb.getNoTelpOrtu());
            pstmt.setString(33, bb.getNamaPenjamin());
            pstmt.setString(34, bb.getAlmtPenjamin());
            pstmt.setString(35, bb.getRTPenjamin());
            pstmt.setString(36, bb.getRWPenjamin());
            pstmt.setString(41, bb.getKodePosPenjamin());
            pstmt.setString(42, bb.getNoTelpPenjamin());
            int i = pstmt.executeUpdate();
            System.out.println("testing");
            return i;
        } catch (Exception e) {
            System.out.println(e);
            return 0;
        }
    }

    public List getAlldetails() throws SQLException, ClassNotFoundException {

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select * from formpermohonan");
        ResultSet rs = pstmt.executeQuery();
        List list = new ArrayList();
        while (rs.next()) {
            Kipmol ubean = new Kipmol();
            ubean.setNamaPemohon(rs.getString(5));
            ubean.setNIKPemohon(rs.getString(6));
            ubean.setNoKK(rs.getString(7));
            ubean.setAlmtPemohon(rs.getString(8));
            ubean.setRTPemohon(rs.getString(9));
            ubean.setRWPemohon(rs.getString(10));
            ubean.setKodePosPemohon(rs.getString(15));
            ubean.setNoTelpPemohon(rs.getString(16));
            ubean.setAlasan(rs.getString(17));
            ubean.setAlmtYgDituju(rs.getString(18));
            ubean.setRTDituju(rs.getString(19));
            ubean.setRWDituju(rs.getString(20));
            ubean.setKodePosDituju(rs.getString(21));
            ubean.setNoTelpDituju(rs.getString(22));
            ubean.setNamaOrtu(rs.getString(23));
            ubean.setAlmtOrtu(rs.getString(24));
            ubean.setRTOrtu(rs.getString(25));
            ubean.setRWOrtu(rs.getString(26));
            ubean.setKodePosOrtu(rs.getString(31));
            ubean.setNoTelpOrtu(rs.getString(32));
            ubean.setNamaPenjamin(rs.getString(33));
            ubean.setAlmtPenjamin(rs.getString(34));
            ubean.setRTPenjamin(rs.getString(35));
            ubean.setRWPenjamin(rs.getString(36));
            ubean.setKodePOsPenjamin(rs.getString(41));;
            ubean.setNoTelpPenjamin(rs.getString(42));
            list.add(ubean);

        }
        return list;
    }

//    public Kipmol getDetails(String uname) throws SQLException, ClassNotFoundException {
//        //here we will write code to get a single record from database
//        Connection con = createConnection();
//        PreparedStatement pstmt = con.prepareStatement("select status from formpermohonan where nikpemohon=?");
//        pstmt.setString(1, uname);
//        ResultSet rs = pstmt.executeQuery();
//
//        Kipmol ubean = new Kipmol();
//        while (rs.next()) {
//
//            ubean.setUserName(rs.getString(1));
//            ubean.setDateOfBirth(rs.getString(2));
//            ubean.setEmail(rs.getString(3));
//            ubean.setPhoneNo(rs.getString(4));
//        }
//        ubean.setAction("update");
//        return ubean;
//    }
//
//    public void UpateDetails(Kipmol ubean, String name) throws SQLException, ClassNotFoundException {
//
//        Connection con = createConnection();
//        PreparedStatement pstmt = con.prepareStatement("update userdetails set DateOfBirth=?,email=?,phoneNo=? where userName=? ");
//        //set values to prepared statement object by getting values from bean object
//        pstmt.setString(1, ubean.getDateOfBirth());
//        pstmt.setString(2, ubean.getEmail());
//        pstmt.setString(3, ubean.getPhoneNo());
//        pstmt.setString(4, name);
//        pstmt.executeUpdate();
//
//    }
//
//    public void deleteDetails(String uname) throws SQLException, ClassNotFoundException {
//        //here we will write code to get a single record from database
//        Connection con = createConnection();
//        PreparedStatement pstmt = con.prepareStatement("delete from userdetails where userName=?");
//        pstmt.setString(1, uname);
//        pstmt.executeUpdate();
//    }
//
//    public Kipmol cekLogin(String username, String pass) throws SQLException, ClassNotFoundException {
//        //here we will write code to get a single record from database
//        Connection con = createConnection();
//        PreparedStatement pstmt = con.prepareStatement("select userName, password from userpass where username=? AND password=?");
//        pstmt.setString(1, username);
//        pstmt.setString(2, pass);
//        ResultSet rs = pstmt.executeQuery();
//
//        Kipmol ubean = new Kipmol();
//        while (rs.next()) {
//            ubean.setAkun(rs.getString(1));
//            ubean.setPass(rs.getString(2));
//        }
//        ubean.setAction("login");
//        return ubean;
//    }
//
//    public Kipmol kota(String username, String pass) throws SQLException, ClassNotFoundException {
//        //here we will write code to get a single record from database
//        Connection con = createConnection();
//        PreparedStatement pstmt = con.prepareStatement("SELECT * FROM inf_lokasi where lokasi_kabupatenkota=0 and lokasi_kecamatan=0 and lokasi_kelurahan=0 order by lokasi_nama");
//        pstmt.setString(1, username);
//        pstmt.setString(2, pass);
//        ResultSet rs = pstmt.executeQuery();
//
//        Kipmol ubean = new Kipmol();
//        while (rs.next()) {
//            ubean.setAkun(rs.getString(1));
//            ubean.setPass(rs.getString(2));
//        }
//        ubean.setAction("login");
//        return ubean;
//    }
//
    public List getProvinsi() throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database

        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select * from provinsi");
        ResultSet rs = pstmt.executeQuery();
        List list = new ArrayList();
        while (rs.next()) {
            Kipmol ubean = new Kipmol();
            ubean.setIdTabel(rs.getString(1));
            ubean.setNamaTabel(rs.getString(2));

            list.add(ubean);

        }
        return list;
    }
    public List getKabupaten(String uname) throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select * from kabupaten where id_prov=?");
        pstmt.setString(1, uname);
        ResultSet rs = pstmt.executeQuery();

        List list = new ArrayList();
        while (rs.next()) {
            Kipmol ubean = new Kipmol();
            ubean.setIdTabel(rs.getString(1));
            ubean.setNamaTabel(rs.getString(3));

            list.add(ubean);

        }
        return list;
    }

    public List getKecamatan(String uname) throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select * from kecamatan where id_kabkota=?");
        pstmt.setString(1, uname);
        ResultSet rs = pstmt.executeQuery();

        List list = new ArrayList();
        while (rs.next()) {
            Kipmol ubean = new Kipmol();
            ubean.setIdTabel(rs.getString(1));
            ubean.setNamaTabel(rs.getString(3));

            list.add(ubean);

        }
        return list;
    }

    public List getDesa(String uname) throws SQLException, ClassNotFoundException {
        //here we will write code to get a single record from database
        Connection con = createConnection();
        PreparedStatement pstmt = con.prepareStatement("select * from desa where id_kecamatan=?");
        pstmt.setString(1, uname);
        ResultSet rs = pstmt.executeQuery();

        List list = new ArrayList();
        while (rs.next()) {
            Kipmol ubean = new Kipmol();
            ubean.setIdTabel(rs.getString(1));
            ubean.setNamaTabel(rs.getString(3));

            list.add(ubean);

        }
        return list;
    }
}
